<?php
namespace plugins\excel\admin;
use app\common\controller\Common;
use app\common\model\SystemAnnex as AnnexModel;
use think\Db;

defined('IN_SYSTEM') or die('Access Denied');

class Excel extends Common
{

    /**
     * 导入数据
     * @link http://v2.doc.hisiphp.com/1103044
     */
    public function import($params)
    {
        require_once dirname(__FILE__) . '/../phpexcel/Classes/PHPExcel/IOFactory.php';

        if (isset($params['file_path']) && !empty($params['file_path'])) {

            $filePath = $params['file_path'];

        } else {

            $upload = AnnexModel::fileUpload(['group' => 'temp', 'driver' => 'local']);
            if (isset($upload['data']['file'])) {
                $filePath = $upload['data']['file'];
            } else {
                return false;
            }

        }

        $startLine  = isset($params['start']) ? $params['start'] : 2;
        $fileType   = \PHPExcel_IOFactory::identify($filePath);
        $reader     =  \PHPExcel_IOFactory::createReader($fileType);

        $reader->setReadDataOnly(true);
		if(isset($params['data']) && !empty($params['data'])){
			$data=$params['data'];
		}else{
			$data = $reader->load($filePath)->getActiveSheet()->toArray(null, true, true, true);
		}
        if (isset($params['fields']) && ( isset($params['model']) || isset($params['table']) )) {

            $results = [];
            if (isset($params['validate']) && !empty($params['validate'])) {//先验证后入库

                for ($i = $startLine; $i < count($data); $i++) {

                    $sqlmap = [];

                    foreach($data[$i] as $kk => $vv) {

                        if (isset($params['fields'][$kk])) {
                            $sqlmap[$params['fields'][$kk]] = $vv;
                        }

                    }

                    // 验证数据
                    $result = $this->validate($sqlmap, $params['validate']);
                    if ($result !== true) {
                        continue;
                    }

                    // 写入数据
                    if ($params['table']) {

                        $results[] = Db::name($params['table'])->strict(false)->insertGetId($sqlmap);

                    } else {

                        $res = model($params['model'])->save($sqlmap);

                        if ($res !== false) {
                            $results[] = $res->id;
                        }

                    }
                }

            } else {// 无需验证数据,直接入库

                for ($i = $startLine; $i <= count($data); $i++) {

                    $sqlmap = [];

                    foreach($data[$i] as $kk => $vv) {

                        if (isset($params['fields'][$kk])) {
                            $sqlmap[$params['fields'][$kk]] = $vv;
                        }

                    }

                    // 写入数据
                    if ($params['table']) {
                        $results[] = Db::name($params['table'])->strict(false)->insertGetId($sqlmap);

                    } else {

                        $res = model($params['model'])->save($sqlmap);

                        if ($res !== false) {
                            $results[] = $res->id;
                        }

                    }
                }
            }

            return $results;

        } elseif (isset($params['fields'])) {

            $results = [];
            if (isset($params['fields'])) {

                for ($i = $startLine; $i < count($data); $i++) {

                    foreach($data[$i] as $kk => $vv) {

                        if (isset($params['fields'][$kk])) {
                            $results[$i][$params['fields'][$kk]] = $vv;
                        }

                    }

                }

            }

            return $results;

        } else {

            return $data;

        }

    }

    /**
     * 导出数据
     * @link http://v2.doc.hisiphp.com/1103044
     */
    public function export($params)
    {
        require_once __DIR__ . '/../phpexcel/Classes/PHPExcel.php';
        require_once __DIR__ . '/../phpexcel/Classes/PHPExcel/Worksheet/Drawing.php';

        // 多工作表 示例结构
        // $params['sheet'] = [
        //     [
        //         'head' => ['编号', '公司名', '姓名', '联系电话'], // 表头
        //         'data' => [// 导出的数据集
        //             [
        //                 '1', '某某互联网公司', '张三', '13000000000',
        //             ],
        //             [
        //                 '2', '某某网络科技公司', '李四', '13111111111',
        //             ],
        //         ],
        //     ],
        //     [
        //         'head' => ['公司名', '姓名', '联系电话'],
        //     ],
        // ];

        // 单工作表 示例结构
        // $params['sheet'] = [
        //     'head' => ['编号', '公司名', '姓名', '联系电话'], // 表头
        //     'data' => [// 导出的数据集
        //             [
        //                 '1', '某某互联网公司', '张三', '13000000000',
        //             ],
        //             [
        //                 '2', '某某网络科技公司', '李四', '13111111111',
        //             ],
        //     ],
        // ];

        if (isset($params['sheet']['head'])) {
            $params['sheet'][0]['head'] = $params['sheet']['head'];
            $params['sheet'][0]['data'] = $params['sheet']['data'];
            unset($params['sheet']['head'], $params['sheet']['data']);
        }

        $fileName = isset($params['file']) ? $params['file'].'.xlsx' : date('Y-m-d').'.xlsx';
        $phpExcel = new \PHPExcel();

        foreach($params['sheet'] as $k => $v) {
            $phpExcel->createSheet();
            $sheet = $phpExcel->setActiveSheetIndex($k);
            $letter = [];
            // 创建表头
            foreach($v['head'] as $kk => $vv) {
                $key =  is_numeric($kk) ? self::numberToLetter($kk) : $kk;
                $letter[] = $key;
                $sheet->setCellValue($key.'1', $vv);
                $sheet->getColumnDimension($key)->setWidth(20);
                $sheet->getRowDimension(1)->setRowHeight(30);
                $sheet->getStyle($key . "1")->getFont()->setBold(true);
                $sheet->getStyle($key . "1")->getAlignment()->setVertical('center');
                $sheet->getStyle($key . "1")->getFont()->getColor()->setARGB("FF0000FF");
            }

            // 写入数据
            if (isset($v['data'])) {
                foreach($v['data'] as $kk => $vv) {
                    $cell = [];
                    $hasPic = false;
                    foreach ($vv as $kkk => $vvv) {
                        $cell[] = $vvv;
                        if (is_array($vvv) && isset($vvv['pic'])) $hasPic = true;
                    }

                    // 设置行高
                    $sheet->getRowDimension($kk+2)->setRowHeight($hasPic?100:22);

                    foreach($cell as $kkk => $vvv) {
                        $key =  is_numeric($kkk) ? self::numberToLetter($kkk) : $kkk;
                        $key .= $kk+2;
                        $sheet->getStyle($key)->getAlignment()->setVertical('center');

                        if (is_array($vvv) && isset($vvv['pic'])) {
							$objDrawing = new \PHPExcel_Worksheet_Drawing();
							// 设置图片路径 切记：只能是本地图片
							$objDrawing->setPath($vvv['pic']);
							// 设置图片高度
							$objDrawing->setWidth(100);
							// $objDrawing->setHeight(80);
							// 设置图片要插入的单元格
							$objDrawing->setCoordinates($key);
							// 设置图片所在单元格的格式
							$objDrawing->setOffsetX(0);
							$objDrawing->setRotation(0);
							$objDrawing->getShadow()->setVisible(false);
							$objDrawing->setWorksheet($sheet);
						} else {
                        	$sheet->setCellValue($key, $vvv);
						}
                    }
                }
            }
        }

        header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
        header('Content-Disposition: attachment;filename="'.$fileName.'"');
        header('Cache-Control: max-age=0');
        header('Cache-Control: max-age=1');
        header ('Last-Modified: '.gmdate('D, d M Y H:i:s').' GMT');
        header ('Cache-Control: cache, must-revalidate');
        header ('Pragma: public');

        $objWriter = \PHPExcel_IOFactory::createWriter($phpExcel, 'Excel2007');
        $objWriter->save('php://output');
        exit;

    }

    // 数字转字母
    protected function numberToLetter($i, $s = 65) {
        $str = '';
        if (floor($i / 26) > 0) {
            $str .= $this->numberToLetter(floor($i / 26)-1);
        }
        return $str . chr($i % 26 + $s);
    }
}